package com.zzjr.tools.java.ssm.utils;

import com.zzjr.tools.java.ssm.constants.AutoCodeConstants;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Description: 数据库工具类
 * 
 * @Date Create on 2015年10月13日
 * @author <a href="mailto:Aromarich@zuozh.com">Aromarich</a>
 * @since version1.0 Copyright 2015 ZZJR All Rights Reserved.
 */
public class DBUtils {
	
	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getDBConnection(String url, String username, String pwd) {
		Connection dbConn = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			dbConn = (Connection) DriverManager.getConnection(url, username, pwd);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		
		return dbConn;
	}
	
	/**
	 * 获取数据库名称列表
	 */
	public static List<String> getDbList(Connection dbConn) {
		List<String> dbNameList = new ArrayList<String>();
		try {
			DatabaseMetaData dmd = (DatabaseMetaData) dbConn.getMetaData();
			ResultSet rs = dmd.getCatalogs();
			while (rs.next()) {
				dbNameList.add(rs.getString("TABLE_CAT"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("查询到的数据库名为:" + dbNameList);
		
		return dbNameList;
	}
	
	/**
	 * 获取数据库名称获取数据库表列表
	 */
	public static List<String> getTableNameList(Connection dbConn, String dbName) {
		List<String> tableNameList = new ArrayList<String>();
		try {
			DatabaseMetaData dmd = (DatabaseMetaData) dbConn.getMetaData();
			ResultSet rs = dmd.getTables(dbName, null, "%", null);
			while (rs.next()) {
				tableNameList.add(rs.getString("TABLE_NAME"));
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("查询到的表名为:" + tableNameList);
		return tableNameList;
	}
	
	/**
	 * 根据表名获取所有的字段名称
	 * 
	 * @param tbname
	 * @return
	 */
	public static String[] getColumnNameByTableName(String tbname) {
		String[] columnArr = null;
		try {
			Connection dbConn = AutoCodeConstants.DB_CONN;
			// 设置数据库名
			dbConn.createStatement().execute("use " + AutoCodeConstants.DB_NAME);
			ResultSet rs = dbConn.createStatement().executeQuery("select * from " + tbname + " limit 1");
			ResultSetMetaData rss = rs.getMetaData();
			int columnCount = rss.getColumnCount();
			columnArr = new String[columnCount];
			System.out.println("Column's count : " + columnCount);
			for (int i = 0; i < columnCount; i++) {
				System.out.println(rss.getColumnName(i + 1));
				columnArr[i] = rss.getColumnName(i + 1);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return columnArr;
	}
	
	/**
	 * 根据表名获取所有的字段类型
	 * 
	 * @param tableName
	 * @return
	 */
	public static String[] getColumnTypeByTableName(String tableName) {
		String[] columnArr = null;
		try {
			Connection dbConn = AutoCodeConstants.DB_CONN;
			// 设置数据库名
			dbConn.createStatement().execute("use " + AutoCodeConstants.DB_NAME);
			ResultSet rs = dbConn.createStatement().executeQuery("select * from " + tableName + " limit 1");
			ResultSetMetaData rss = rs.getMetaData();
			int columnCount = rss.getColumnCount();
			columnArr = new String[columnCount];
			System.out.println("Column's count : " + columnCount);
			for (int i = 0; i < columnCount; i++) {
				System.out.println(rss.getColumnName(i + 1));
				columnArr[i] = rss.getColumnTypeName(i + 1);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return columnArr;
	}
	
	/**
	 * 根据表名获取所有的字段大小
	 * 
	 * @param tableName
	 * @return
	 */
	public static int[] getColumnTypeRangeByTableName(String tableName) {
		int[] columnArr = null;
		try {
			Connection dbConn = AutoCodeConstants.DB_CONN;
			// 设置数据库名
			dbConn.createStatement().execute("use " + AutoCodeConstants.DB_NAME);
			ResultSet rs = dbConn.createStatement().executeQuery("select * from " + tableName + " limit 1");
			ResultSetMetaData rss = rs.getMetaData();
			int columnCount = rss.getColumnCount();
			columnArr = new int[columnCount];
			System.out.println("Column's count : " + columnCount);
			for (int i = 0; i < columnCount; i++) {
				System.out.println(rss.getColumnName(i + 1));
				columnArr[i] = rss.getColumnDisplaySize(i + 1);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return columnArr;
	}
	
	/**
	 * 根据表名获取所有的字段注释
	 * 
	 * @param tableName
	 * @return
	 */
	public static String[] getColumnCommentByTableName(String tableName) {
		String[] columnArr = null;
		try {
			Connection dbConn = AutoCodeConstants.DB_CONN;
			
			// 获取tableName表列信息
			DatabaseMetaData databaseMetaData = dbConn.getMetaData();
			ResultSet rs = databaseMetaData.getColumns(AutoCodeConstants.DB_NAME, null, tableName, null);
			rs.last();// 移到最后一行
			int columnCount = rs.getRow();
			rs.beforeFirst();// 移动到首行
			columnArr = new String[columnCount];
			int index = 0;
			while (rs.next()) {
				// System.out.println(rs.getString("REMARKS"));
				columnArr[index] = rs.getString("REMARKS");
				index++;
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return columnArr;
	}
}
